Re: [SQL] Some questions

At 20:09 +0200 on 13/11/98, Vladimir Litovka wrote:


>
>  There is table of character types in manual, where the 'text' type suggested
>  as the best choice. 'text' is variable length type and I think, that
>  fixed length's types (such as char(n)) will be faster, than variable
>  length's ones? Am I wrong?

Basically, yes. But it depends also on the order of the field. Every field
after the first variable-length field will take longer to access,
regardless of whether in itself it is variable or not.

>  The second question is more complex :) It about CREATE TABLE statement. Is
>  there difference between following statements?
>
>   create table aaa (i int2 UNIQUE);
>  and
>   create table aaa (i int2, CONSTRAINT aaa_i_key UNIQUE(i));
>
>  First case uses 'column constraint' and second - 'table constraint'. Manual
>  says: "A column constraint is an integrity constraint defined as part of a
>  column definition, and logically becomes a table constraint as soon as it
>  is created". Am I understand correctly - it is never mind where to define
>  (any) constraints - all of them does the same?

Not exactly. A column constraint is a private case of a table constraint,
which is limited only to one column. You can say: "I want this column to be
unique, divisable by 10, and positive".

But a table constraint allows you to define a condition on more than one
column in the same table. For example, you can say "I want column A to be a
multiple of column B". One of the constraints I needed once said "Either
column A or column B may be null, but not both of them together".

So, of course, you may add the column constraints to the table constraints,
because they *are* table constraints. However, it is more elegant to put
them together with the column definition. This lets whoever reads the table
definition see immediately all the information there is to know about the
specific column - that it has type integer, default value 13, it may not be
null, is unique, and so forth.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma